﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommonLibrary.DataModel.Basic;
using System.Data.SqlClient;
using CommonLibrary.DB;
using System.Data;

namespace ServiceApplication.Dao.Basic
{
    public class StaffDao
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="staffEntity"></param>
        /// <returns>刚插入的主键</returns>
        public int AddEntity(StaffEntity staffEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@staff_id", staffEntity.staff_id));
            parameterList.Add(new SqlParameter("@staff_name", staffEntity.staff_name));
            parameterList.Add(new SqlParameter("@dept_id", staffEntity.dept_id));
            parameterList.Add(new SqlParameter("@duty_id", staffEntity.duty_id));
            parameterList.Add(new SqlParameter("@staff_sex", staffEntity.staff_sex));
            parameterList.Add(new SqlParameter("@staff_homeaddr", staffEntity.staff_homeaddr));
            parameterList.Add(new SqlParameter("@staff_img", staffEntity.staff_img));
            parameterList.Add(new SqlParameter("@staff_sfz", staffEntity.staff_sfz));
            parameterList.Add(new SqlParameter("@staff_postnum", staffEntity.staff_postnum));
            parameterList.Add(new SqlParameter("@staff_marrige", staffEntity.staff_marrige));
            parameterList.Add(new SqlParameter("@staff_study", staffEntity.staff_study));
            parameterList.Add(new SqlParameter("@staff_school", staffEntity.staff_school));
            parameterList.Add(new SqlParameter("@staff_major", staffEntity.staff_major));
            parameterList.Add(new SqlParameter("@staff_politics", staffEntity.staff_politics));
            parameterList.Add(new SqlParameter("@staff_birth", staffEntity.staff_birth));
            parameterList.Add(new SqlParameter("@staff_interest", staffEntity.staff_interest));
            parameterList.Add(new SqlParameter("@staff_zybl", staffEntity.staff_zybl));
            parameterList.Add(new SqlParameter("@staff_liveaddr", staffEntity.staff_liveaddr));
            parameterList.Add(new SqlParameter("@staff_livenum", staffEntity.staff_livenum));
            parameterList.Add(new SqlParameter("@staff_mobile", staffEntity.staff_mobile));
            parameterList.Add(new SqlParameter("@staff_shortnum", staffEntity.staff_shortnum));
            parameterList.Add(new SqlParameter("@staff_officenum", staffEntity.staff_officenum));
            parameterList.Add(new SqlParameter("@staff_rzsj", staffEntity.staff_rzsj));
            parameterList.Add(new SqlParameter("@staff_lxr", staffEntity.staff_lxr));
            parameterList.Add(new SqlParameter("@staff_lxrphone", staffEntity.staff_lxrphone));
            parameterList.Add(new SqlParameter("@staff_jsr", staffEntity.staff_jsr));
            parameterList.Add(new SqlParameter("@staff_gzjl", staffEntity.staff_gzjl));
            parameterList.Add(new SqlParameter("@staff_jtcy", staffEntity.staff_jtcy));
            parameterList.Add(new SqlParameter("@staff_email", staffEntity.staff_email));
            parameterList.Add(new SqlParameter("@staff_qq", staffEntity.staff_qq));
            parameterList.Add(new SqlParameter("@staff_fax", staffEntity.staff_fax));
            parameterList.Add(new SqlParameter("@staff_height", staffEntity.staff_height));
            parameterList.Add(new SqlParameter("@staff_nation", staffEntity.staff_nation));
            parameterList.Add(new SqlParameter("@staff_sygz", staffEntity.staff_sygz));
            parameterList.Add(new SqlParameter("@staff_zzgz", staffEntity.staff_zzgz));
            parameterList.Add(new SqlParameter("@staff_sfsb", staffEntity.staff_sfsb));
            parameterList.Add(new SqlParameter("@staff_code", staffEntity.staff_code));
            parameterList.Add(new SqlParameter("@staff_descn", staffEntity.staff_descn));
            parameterList.Add(new SqlParameter("@staff_ctime", staffEntity.staff_ctime));
            parameterList.Add(new SqlParameter("@staff_officeaddr", staffEntity.staff_officeaddr));
            parameterList.Add(new SqlParameter("@staff_fjh", staffEntity.staff_fjh));
            parameterList.Add(new SqlParameter("@staff_erp", staffEntity.staff_erp));
            parameterList.Add(new SqlParameter("@staff_empsw", staffEntity.staff_empsw));
            parameterList.Add(new SqlParameter("@staff_kybz", staffEntity.staff_kybz));

            string sql = "insert into jc_staff (staff_name,dept_id,duty_id,staff_sex,staff_homeaddr,staff_img,staff_sfz, staff_postnum, " +
                " staff_marrige,staff_study,staff_school,staff_major,staff_politics,staff_birth,staff_interest,staff_zybl,staff_liveaddr,staff_livenum,staff_mobile, " +
                " staff_shortnum,staff_officenum,staff_rzsj,staff_lxr,staff_lxrphone,staff_jsr,staff_gzjl,staff_jtcy,staff_email,staff_qq, " +
                " staff_fax,staff_height,staff_nation,staff_sygz,staff_zzgz,staff_sfsb,staff_code,staff_descn,staff_ctime,staff_officeaddr,staff_fjh,staff_erp,staff_empsw,staff_kybz) values " +
                " (@staff_name,@dept_id,@duty_id,@staff_sex,@staff_homeaddr,@staff_img,@staff_sfz,@staff_postnum, " +
                " @staff_marrige,@staff_study,@staff_school,@staff_major,@staff_politics,@staff_birth,@staff_interest,@staff_zybl,@staff_liveaddr,@staff_livenum,@staff_mobile, " +
                " @staff_shortnum,@staff_officenum,@staff_rzsj,@staff_lxr,@staff_lxrphone,@staff_jsr,@staff_gzjl,@staff_jtcy,@staff_email,@staff_qq, " +
                " @staff_fax,@staff_height,@staff_nation,@staff_sygz,@staff_zzgz,@staff_sfsb,@staff_code,@staff_descn,@staff_ctime,@staff_officeaddr,@staff_fjh,@staff_erp,@staff_empsw,@staff_kybz); " +
                " select @@identity";
            string staff_id = DBExecuteUtil.getUniqueResult(sql, parameterList.ToArray());
            return int.Parse(staff_id);
        }

        public void DleteEntity(int staff_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@staff_id", staff_id));
            string sql = "delete from jc_staff where staff_id=@staff_id ";
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public void UpdateEntity(StaffEntity staffEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@staff_id", staffEntity.staff_id));
            parameterList.Add(new SqlParameter("@staff_name", staffEntity.staff_name));
            parameterList.Add(new SqlParameter("@dept_id", staffEntity.dept_id));
            parameterList.Add(new SqlParameter("@duty_id", staffEntity.duty_id));
            parameterList.Add(new SqlParameter("@staff_sex", staffEntity.staff_sex));
            parameterList.Add(new SqlParameter("@staff_homeaddr", staffEntity.staff_homeaddr));
            parameterList.Add(new SqlParameter("@staff_img", staffEntity.staff_img));
            parameterList.Add(new SqlParameter("@staff_sfz", staffEntity.staff_sfz));
            parameterList.Add(new SqlParameter("@staff_postnum", staffEntity.staff_postnum));
            parameterList.Add(new SqlParameter("@staff_marrige", staffEntity.staff_marrige));
            parameterList.Add(new SqlParameter("@staff_study", staffEntity.staff_study));
            parameterList.Add(new SqlParameter("@staff_school", staffEntity.staff_school));
            parameterList.Add(new SqlParameter("@staff_major", staffEntity.staff_major));
            parameterList.Add(new SqlParameter("@staff_politics", staffEntity.staff_politics));
            parameterList.Add(new SqlParameter("@staff_birth", staffEntity.staff_birth));
            parameterList.Add(new SqlParameter("@staff_interest", staffEntity.staff_interest));
            parameterList.Add(new SqlParameter("@staff_zybl", staffEntity.staff_zybl));
            parameterList.Add(new SqlParameter("@staff_liveaddr", staffEntity.staff_liveaddr));
            parameterList.Add(new SqlParameter("@staff_livenum", staffEntity.staff_livenum));
            parameterList.Add(new SqlParameter("@staff_mobile", staffEntity.staff_mobile));
            parameterList.Add(new SqlParameter("@staff_shortnum", staffEntity.staff_shortnum));
            parameterList.Add(new SqlParameter("@staff_officenum", staffEntity.staff_officenum));
            parameterList.Add(new SqlParameter("@staff_rzsj", staffEntity.staff_rzsj));
            parameterList.Add(new SqlParameter("@staff_lxr", staffEntity.staff_lxr));
            parameterList.Add(new SqlParameter("@staff_lxrphone", staffEntity.staff_lxrphone));
            parameterList.Add(new SqlParameter("@staff_jsr", staffEntity.staff_jsr));
            parameterList.Add(new SqlParameter("@staff_gzjl", staffEntity.staff_gzjl));
            parameterList.Add(new SqlParameter("@staff_jtcy", staffEntity.staff_jtcy));
            parameterList.Add(new SqlParameter("@staff_email", staffEntity.staff_email));
            parameterList.Add(new SqlParameter("@staff_qq", staffEntity.staff_qq));
            parameterList.Add(new SqlParameter("@staff_fax", staffEntity.staff_fax));
            parameterList.Add(new SqlParameter("@staff_height", staffEntity.staff_height));
            parameterList.Add(new SqlParameter("@staff_nation", staffEntity.staff_nation));
            parameterList.Add(new SqlParameter("@staff_sygz", staffEntity.staff_sygz));
            parameterList.Add(new SqlParameter("@staff_zzgz", staffEntity.staff_zzgz));
            parameterList.Add(new SqlParameter("@staff_sfsb", staffEntity.staff_sfsb));
            parameterList.Add(new SqlParameter("@staff_code", staffEntity.staff_code));
            parameterList.Add(new SqlParameter("@staff_descn", staffEntity.staff_descn));
            parameterList.Add(new SqlParameter("@staff_ctime", staffEntity.staff_ctime));
            parameterList.Add(new SqlParameter("@staff_officeaddr", staffEntity.staff_officeaddr));
            parameterList.Add(new SqlParameter("@staff_fjh", staffEntity.staff_fjh));
            parameterList.Add(new SqlParameter("@staff_erp", staffEntity.staff_erp));
            parameterList.Add(new SqlParameter("@staff_empsw", staffEntity.staff_empsw));
            parameterList.Add(new SqlParameter("@staff_kybz", staffEntity.staff_kybz));

            string sql = "update jc_staff set staff_name=@staff_name,dept_id=@dept_id,duty_id=@duty_id,staff_sex=@staff_sex, " +
                " staff_homeaddr=@staff_homeaddr,staff_img=@staff_img,staff_sfz=@staff_sfz, staff_postnum=@staff_postnum, " +
                " staff_marrige=@staff_marrige,staff_study=@staff_study,staff_school=@staff_school,staff_major=@staff_major, " +
                " staff_politics=@staff_politics,staff_birth=@staff_birth,staff_interest=@staff_interest,staff_zybl=@staff_zybl, " +
                " staff_liveaddr=@staff_liveaddr,staff_livenum=@staff_livenum,staff_mobile=@staff_mobile,staff_shortnum=@staff_shortnum,staff_officenum=@staff_officenum, " +
                " staff_rzsj=@staff_rzsj,staff_lxr=@staff_lxr,staff_lxrphone=@staff_lxrphone,staff_jsr=@staff_jsr,staff_gzjl=@staff_gzjl, " +
                " staff_jtcy=@staff_jtcy,staff_email=@staff_email,staff_qq=@staff_qq,staff_fax=@staff_fax,staff_height=@staff_height, " +
                " staff_nation=@staff_nation,staff_sygz=@staff_sygz,staff_zzgz=@staff_zzgz,staff_sfsb=@staff_sfsb,staff_code=@staff_code, " +
                " staff_descn=@staff_descn,staff_ctime=@staff_ctime,staff_officeaddr=@staff_officeaddr,staff_fjh=@staff_fjh,staff_erp=@staff_erp," +
                " staff_empsw=@staff_empsw,staff_kybz=@staff_kybz  where staff_id=@staff_id ";
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());

        }

        public StaffEntity QueryEntity(int staff_id)
        {
            StaffEntity staffEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@staff_id", staff_id));
            string sql = "select staff_id,staff_name,dept_id,duty_id,staff_sex,staff_homeaddr,staff_img,staff_sfz, staff_postnum, " +
                " staff_marrige,staff_study,staff_school,staff_major,staff_politics,staff_birth,staff_interest,staff_zybl,staff_liveaddr,staff_livenum,staff_mobile, " +
                " staff_shortnum,staff_officenum,staff_rzsj,staff_lxr,staff_lxrphone,staff_jsr,staff_gzjl,staff_jtcy,staff_email,staff_qq, " +
                " staff_fax,staff_height,staff_nation,staff_sygz,staff_zzgz,staff_sfsb,staff_code,staff_descn,staff_ctime,staff_officeaddr,staff_fjh,staff_erp,staff_empsw,staff_kybz  " +
                " from jc_staff where staff_id=@staff_id";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (result == null) return null;

            staffEntity = new StaffEntity();
            staffEntity.staff_id = int.Parse(result[0][0]);
            staffEntity.staff_name = result[0][1];
            staffEntity.dept_id = int.Parse(result[0][2]);
            staffEntity.duty_id = int.Parse(result[0][3]);
            staffEntity.staff_sex = result[0][4];
            staffEntity.staff_homeaddr = result[0][5];
            staffEntity.staff_img = result[0][6];
            staffEntity.staff_sfz = result[0][7];
            staffEntity.staff_postnum = result[0][8];
            staffEntity.staff_marrige = result[0][9];
            staffEntity.staff_study = result[0][10];
            staffEntity.staff_school = result[0][11];
            staffEntity.staff_major = result[0][12];
            staffEntity.staff_politics = result[0][13];
            staffEntity.staff_birth = result[0][14];
            staffEntity.staff_interest = result[0][15];
            staffEntity.staff_zybl = result[0][16];
            staffEntity.staff_liveaddr = result[0][17];
            staffEntity.staff_livenum = result[0][18];
            staffEntity.staff_mobile = result[0][19];
            staffEntity.staff_shortnum = result[0][20];
            staffEntity.staff_officenum = result[0][21];
            staffEntity.staff_rzsj = result[0][22];
            staffEntity.staff_lxr = result[0][23];
            staffEntity.staff_lxrphone = result[0][24];
            staffEntity.staff_jsr = result[0][25];
            staffEntity.staff_gzjl = result[0][26];
            staffEntity.staff_jtcy = result[0][27];
            staffEntity.staff_email = result[0][28];
            staffEntity.staff_qq = result[0][29];
            staffEntity.staff_fax = result[0][30];
            staffEntity.staff_height = result[0][31];
            staffEntity.staff_nation = result[0][32];
            staffEntity.staff_sygz = decimal.Parse(result[0][33] == null ? "0" : result[0][33]);
            staffEntity.staff_zzgz = decimal.Parse(result[0][34] == null ? "0" : result[0][34]);
            staffEntity.staff_sfsb = result[0][35];
            staffEntity.staff_code = result[0][36];
            staffEntity.staff_descn = result[0][37];
            staffEntity.staff_ctime = result[0][38];
            staffEntity.staff_officeaddr = result[0][39];
            staffEntity.staff_fjh = result[0][40];
            staffEntity.staff_erp = result[0][41];
            staffEntity.staff_empsw = result[0][42];
            staffEntity.staff_kybz = result[0][43];
            return staffEntity;
        }

        public StaffEntity QueryEntity(string staff_name)
        {
            StaffEntity staffEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@staff_name", staff_name));
            string sql = "select staff_id,staff_name,dept_id,duty_id,staff_sex,staff_homeaddr,staff_img,staff_sfz, staff_postnum, " +
                " staff_marrige,staff_study,staff_school,staff_major,staff_politics,staff_birth,staff_interest,staff_zybl,staff_liveaddr,staff_livenum,staff_mobile, " +
                " staff_shortnum,staff_officenum,staff_rzsj,staff_lxr,staff_lxrphone,staff_jsr,staff_gzjl,staff_jtcy,staff_email,staff_qq, " +
                " staff_fax,staff_height,staff_nation,staff_sygz,staff_zzgz,staff_sfsb,staff_code,staff_descn,staff_ctime,staff_officeaddr,staff_fjh,staff_erp,staff_empsw,staff_kybz " +
                " from jc_staff where staff_name=@staff_name";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (result == null) return null;

            staffEntity = new StaffEntity();
            staffEntity.staff_id = int.Parse(result[0][0]);
            staffEntity.staff_name = result[0][1];
            staffEntity.dept_id = int.Parse(result[0][2]);
            staffEntity.duty_id = int.Parse(result[0][3]);
            staffEntity.staff_sex = result[0][4];
            staffEntity.staff_homeaddr = result[0][5];
            staffEntity.staff_img = result[0][6];
            staffEntity.staff_sfz = result[0][7];
            staffEntity.staff_postnum = result[0][8];
            staffEntity.staff_marrige = result[0][9];
            staffEntity.staff_study = result[0][10];
            staffEntity.staff_school = result[0][11];
            staffEntity.staff_major = result[0][12];
            staffEntity.staff_politics = result[0][13];
            staffEntity.staff_birth = result[0][14];
            staffEntity.staff_interest = result[0][15];
            staffEntity.staff_zybl = result[0][16];
            staffEntity.staff_liveaddr = result[0][17];
            staffEntity.staff_livenum = result[0][18];
            staffEntity.staff_mobile = result[0][19];
            staffEntity.staff_shortnum = result[0][20];
            staffEntity.staff_officenum = result[0][21];
            staffEntity.staff_rzsj = result[0][22];
            staffEntity.staff_lxr = result[0][23];
            staffEntity.staff_lxrphone = result[0][24];
            staffEntity.staff_jsr = result[0][25];
            staffEntity.staff_gzjl = result[0][26];
            staffEntity.staff_jtcy = result[0][27];
            staffEntity.staff_email = result[0][28];
            staffEntity.staff_qq = result[0][29];
            staffEntity.staff_fax = result[0][30];
            staffEntity.staff_height = result[0][31];
            staffEntity.staff_nation = result[0][32];
            staffEntity.staff_sygz = decimal.Parse(result[0][33] == null ? "0" : result[0][33]);
            staffEntity.staff_zzgz = decimal.Parse(result[0][34] == null ? "0" : result[0][34]);
            staffEntity.staff_sfsb = result[0][35];
            staffEntity.staff_code = result[0][36];
            staffEntity.staff_descn = result[0][37];
            staffEntity.staff_ctime = result[0][38];
            staffEntity.staff_officeaddr = result[0][39];
            staffEntity.staff_fjh = result[0][40];
            staffEntity.staff_erp = result[0][41];
            staffEntity.staff_empsw = result[0][42];
            staffEntity.staff_kybz = result[0][43];
            return staffEntity;
        }

        public StaffEntity QueryByUserId(int user_id)
        {
            StaffEntity staffEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@user_id", user_id));
            string sql = "select s.staff_id,staff_name,dept_id,duty_id,staff_sex,staff_homeaddr,staff_img,staff_sfz, staff_postnum, " +
                " staff_marrige,staff_study,staff_school,staff_major,staff_politics,staff_birth,staff_interest,staff_zybl,staff_liveaddr,staff_livenum,staff_mobile, " +
                " staff_shortnum,staff_officenum,staff_rzsj,staff_lxr,staff_lxrphone,staff_jsr,staff_gzjl,staff_jtcy,staff_email,staff_qq, " +
                " staff_fax,staff_height,staff_nation,staff_sygz,staff_zzgz,staff_sfsb,staff_code,staff_descn,staff_ctime,staff_officeaddr,staff_fjh,staff_erp,staff_empsw,staff_kybz " +
                " from jc_staff s,jc_user u where s.staff_id=u.staff_id and user_id=@user_id";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (result == null) return null;

            staffEntity = new StaffEntity();
            staffEntity.staff_id = int.Parse(result[0][0]);
            staffEntity.staff_name = result[0][1];
            staffEntity.dept_id = int.Parse(result[0][2]);
            staffEntity.duty_id = int.Parse(result[0][3]);
            staffEntity.staff_sex = result[0][4];
            staffEntity.staff_homeaddr = result[0][5];
            staffEntity.staff_img = result[0][6];
            staffEntity.staff_sfz = result[0][7];
            staffEntity.staff_postnum = result[0][8];
            staffEntity.staff_marrige = result[0][9];
            staffEntity.staff_study = result[0][10];
            staffEntity.staff_school = result[0][11];
            staffEntity.staff_major = result[0][12];
            staffEntity.staff_politics = result[0][13];
            staffEntity.staff_birth = result[0][14];
            staffEntity.staff_interest = result[0][15];
            staffEntity.staff_zybl = result[0][16];
            staffEntity.staff_liveaddr = result[0][17];
            staffEntity.staff_livenum = result[0][18];
            staffEntity.staff_mobile = result[0][19];
            staffEntity.staff_shortnum = result[0][20];
            staffEntity.staff_officenum = result[0][21];
            staffEntity.staff_rzsj = result[0][22];
            staffEntity.staff_lxr = result[0][23];
            staffEntity.staff_lxrphone = result[0][24];
            staffEntity.staff_jsr = result[0][25];
            staffEntity.staff_gzjl = result[0][26];
            staffEntity.staff_jtcy = result[0][27];
            staffEntity.staff_email = result[0][28];
            staffEntity.staff_qq = result[0][29];
            staffEntity.staff_fax = result[0][30];
            staffEntity.staff_height = result[0][31];
            staffEntity.staff_nation = result[0][32];
            staffEntity.staff_sygz = decimal.Parse(result[0][33] == null ? "0" : result[0][33]);
            staffEntity.staff_zzgz = decimal.Parse(result[0][34] == null ? "0" : result[0][34]);
            staffEntity.staff_sfsb = result[0][35];
            staffEntity.staff_code = result[0][36];
            staffEntity.staff_descn = result[0][37];
            staffEntity.staff_ctime = result[0][38];
            staffEntity.staff_officeaddr = result[0][39];
            staffEntity.staff_fjh = result[0][40];
            staffEntity.staff_erp = result[0][41];
            staffEntity.staff_empsw = result[0][42];
            staffEntity.staff_kybz = result[0][43];
            return staffEntity;
        }

        public DataTable QueryAll(string staff_kybz)
        {
            string sql = "select staff_id 员工编码,staff_name 员工名称,s.dept_id 部门编码,dept_name 部门名称,s.duty_id 职位编码, " +
                " duty_name 职位名称,staff_sex 性别,staff_homeaddr 家庭住址,staff_img 照片,staff_sfz 身份证, staff_postnum 邮编, " +
                " staff_marrige 婚否,staff_study 学历,staff_school 毕业院校,staff_major 专业,staff_politics 政治面貌,staff_birth 出生日期, " +
                " staff_interest 兴趣,staff_zybl 主要病历,staff_liveaddr 现居地址,staff_livenum 住址电话,staff_mobile 手机,staff_shortnum 短号,staff_officenum 办公电话, " +
                " staff_rzsj 入职时间,staff_lxr 联系人,staff_lxrphone 联系人电话,staff_jsr 介绍人,staff_gzjl 工作经历,staff_jtcy 家庭成员, " +
                " staff_email 邮箱,staff_qq qq,staff_fax 传真,staff_height 身高,staff_nation 民族,staff_sygz 试用工资,staff_zzgz 转正工资, " +
                " staff_sfsb 是否社保,staff_code 员工编号,staff_descn 其它,staff_ctime 创建时间,staff_officeaddr 办公地址,staff_fjh 分机号,staff_erp ERP,staff_empsw 邮件密码,staff_kybz 在职标志 " +
                " from jc_staff s,jc_dept de,jc_duty du where s.dept_id=de.dept_id and s.duty_id=du.duty_id ";
            if (!string.IsNullOrEmpty(staff_kybz))
            {
                sql += " and s.staff_kybz='" + staff_kybz + "'";
            }
            var dt = DBExecuteUtil.querySqlTable(sql);
            return dt;
        }

        public DataTable QueryNoUserAll()
        {
            string sql = "select staff_id 员工编码,staff_name 员工名称,s.dept_id 部门编码,dept_name 部门名称,s.duty_id 职位编码, " +
                " duty_name 职位名称,staff_sex 性别,staff_homeaddr 家庭住址,staff_img 照片,staff_sfz 身份证, staff_postnum 邮编, " +
                " staff_marrige 婚否,staff_study 学历,staff_school 毕业院校,staff_major 专业,staff_politics 政治面貌,staff_birth 出生日期, " +
                " staff_interest 兴趣,staff_zybl 主要病历,staff_liveaddr 现居地址,staff_livenum 住址电话,staff_mobile 手机,staff_shortnum 短号,staff_officenum 办公电话, " +
                " staff_rzsj 入职时间,staff_lxr 联系人,staff_lxrphone 联系人电话,staff_jsr 介绍人,staff_gzjl 工作经历,staff_jtcy 家庭成员, " +
                " staff_email 邮箱,staff_qq qq,staff_fax 传真,staff_height 身高,staff_nation 民族,staff_sygz 试用工资,staff_zzgz 转正工资, " +
                " staff_sfsb 是否社保,staff_code 员工编号,staff_descn 其它,staff_ctime 创建时间,staff_officeaddr 办公地址,staff_fjh 分机号,staff_erp ERP,staff_empsw 邮件密码,staff_kybz 在职标志 " +
                " from jc_staff s,jc_dept de,jc_duty du where s.dept_id=de.dept_id and s.duty_id=du.duty_id and s.staff_id not in (select staff_id from jc_user) ";

            var dt = DBExecuteUtil.querySqlTable(sql);
            return dt;
        }

        public DataTable QueryByDeptId(string dept_id, string staff_kybz)
        {
            string sql = "select staff_id 员工编码,staff_name 员工名称,s.dept_id 部门编码,dept_name 部门名称,s.duty_id 职位编码, " +
                " duty_name 职位名称,staff_sex 性别,staff_homeaddr 家庭住址,staff_img 照片,staff_sfz 身份证, staff_postnum 邮编, " +
                " staff_marrige 婚否,staff_study 学历,staff_school 毕业院校,staff_major 专业,staff_politics 政治面貌,staff_birth 出生日期, " +
                " staff_interest 兴趣,staff_zybl 主要病历,staff_liveaddr 现居地址,staff_livenum 住址电话,staff_mobile 手机,staff_shortnum 短号,staff_officenum 办公电话, " +
                " staff_rzsj 入职时间,staff_lxr 联系人,staff_lxrphone 联系人电话,staff_jsr 介绍人,staff_gzjl 工作经历,staff_jtcy 家庭成员, " +
                " staff_email 邮箱,staff_qq qq,staff_fax 传真,staff_height 身高,staff_nation 民族,staff_sygz 试用工资,staff_zzgz 转正工资, " +
                " staff_sfsb 是否社保,staff_code 员工编号,staff_descn 其它,staff_ctime 创建时间,staff_officeaddr 办公地址,staff_fjh 分机号,staff_erp ERP,staff_empsw 邮件密码,staff_kybz 在职标志 " +
                " from jc_staff s,jc_dept de,jc_duty du where s.dept_id=de.dept_id and s.duty_id=du.duty_id and s.dept_id=@dept_id ";
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@dept_id", dept_id));
            if (!string.IsNullOrEmpty(staff_kybz))
            {
                parameterList.Add(new SqlParameter("@staff_kybz", staff_kybz));
                sql += " and s.staff_kybz=@staff_kybz ";
            }
            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }

        public DataTable QueryByLikeName(string staff_name)
        {
            string sql = "select staff_id 员工编码,staff_name 员工名称,s.dept_id 部门编码,dept_name 部门名称,s.duty_id 职位编码, " +
                " duty_name 职位名称,staff_sex 性别,staff_homeaddr 家庭住址,staff_img 照片,staff_sfz 身份证, staff_postnum 邮编, " +
                " staff_marrige 婚否,staff_study 学历,staff_school 毕业院校,staff_major 专业,staff_politics 政治面貌,staff_birth 出生日期, " +
                " staff_interest 兴趣,staff_zybl 主要病历,staff_liveaddr 现居地址,staff_livenum 住址电话,staff_mobile 手机,staff_shortnum 短号,staff_officenum 办公电话, " +
                " staff_rzsj 入职时间,staff_lxr 联系人,staff_lxrphone 联系人电话,staff_jsr 介绍人,staff_gzjl 工作经历,staff_jtcy 家庭成员, " +
                " staff_email 邮箱,staff_qq qq,staff_fax 传真,staff_height 身高,staff_nation 民族,staff_sygz 试用工资,staff_zzgz 转正工资, " +
                " staff_sfsb 是否社保,staff_code 员工编号,staff_descn 其它,staff_ctime 创建时间,staff_officeaddr 办公地址,staff_fjh 分机号,staff_erp ERP,staff_empsw 邮件密码,staff_kybz 在职标志 " +
                " from jc_staff s,jc_dept de,jc_duty du where s.dept_id=de.dept_id and s.duty_id=du.duty_id and s.staff_name like @staff_name ";
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@staff_name", "%" + staff_name + "%"));
            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }
    }
}
